package models;
import java.sql.*;
import java.text.SimpleDateFormat;
import java.util.ArrayList;
import java.util.HashMap;

import play.db.DB;

public class SQLwork 
{
	private static HashMap<String, Integer> temp;
	private static ResultSet resultset;
	
	static int posOld, posNew, negOld, negNew, ratOld, ratNew, noiOld, noiNew, updNew = 0;
	
	public static ArrayList getNoise() throws SQLException
	{
		ArrayList noise = new ArrayList();
		
		resultset = DB.executeQuery("SELECT * from noilist");
		
		while(resultset.next())
		{
			noise.add(resultset.getString("name"));
		}
		
		resultset.close();
		
		noiNew = noise.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Read noise-list - "+noiNew+" entries')");
		
		return noise;
	}
	
	public static ArrayList getUpdates() throws SQLException
	{
		ArrayList updates = new ArrayList();
		
		resultset = DB.executeQuery("SELECT * from updates");
		
		while(resultset.next())
		{
			updates.add(resultset.getString("date")+" - "+resultset.getString("name"));
		}
		
		resultset.close();
		
		updNew = (updates.size()/2);
		DB.execute("INSERT INTO updates (name) VALUES ('Read update-list - "+updNew+" entries')");
		
		return updates;
	}
	
	public static HashMap<String, Integer> getPositiv() throws SQLException
	{
		temp = new HashMap<String, Integer>();
			
		resultset = DB.executeQuery("SELECT * from poslist");

		while(resultset.next())
		{
			temp.put(resultset.getString("name"), resultset.getInt("rating"));
		}
		
		resultset.close();
		
		posNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Read positiv-list - "+posNew+" entries')");
		
		return temp;
	}
	
	public static HashMap<String, Integer> getNegativ() throws SQLException
	{
		temp = new HashMap<String, Integer>();
			
		resultset = DB.executeQuery("SELECT * from neglist");

		while(resultset.next())
		{
			temp.put(resultset.getString("name"), resultset.getInt("rating"));
		}
		
		resultset.close();
		
		negNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Read negativ-list - "+negNew+" entries')");
		
		return temp;
	}
	
	public static HashMap<String, Integer> getRating() throws SQLException
	{
		temp = new HashMap<String, Integer>();
			
		resultset = DB.executeQuery("SELECT * from ratlist");

		while(resultset.next())
		{
			temp.put(resultset.getString("name"), resultset.getInt("rating"));
		}
		
		resultset.close();
		
		ratNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Read rating-list - "+ratNew+" entries')");
		
		return temp;
	}
	
	public static void setNoise(ArrayList noise) throws SQLException
	{
		ArrayList n = noise;
		
		DB.execute("DELETE FROM noilist");
		
		for(int i = 0; i < n.size(); i++)
		{
			DB.execute("INSERT INTO noilist (name) VALUES ('"+ n.get(i) +"')"); 
		}
		
		noiOld = noiNew;
		noiNew = n.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Write rating-list - "+noiNew+" entries "+(noiNew - noiOld)+" new')");
	}
	
	public static void setPositiv(HashMap<String, Integer> pos) 
	{ 
		temp = pos;
		
		DB.execute("DELETE FROM poslist");
		
		for(String elem : temp.keySet())
        {
        	DB.execute("INSERT INTO poslist (name, rating) VALUES ('"+ elem +"', '"+ temp.get(elem) +"')"); 
        }
		
		posOld = posNew;
		posNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Write rating-list - "+posNew+" entries "+(posNew - posOld)+" new')");
    }
	
	public static void setNegativ(HashMap<String, Integer> neg) 
	{ 
		temp = neg;
		
		DB.execute("DELETE FROM neglist");
		
		for(String elem : temp.keySet())
        {
			DB.execute("INSERT INTO neglist (name, rating) VALUES ('"+ elem +"', '"+ temp.get(elem) +"')"); 
        }
		
		negOld = negNew;
		negNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Write rating-list - "+negNew+" entries "+(negNew - negOld)+" new')");
    }
	
	public static void setRating(HashMap<String, Integer> rat) 
	{ 
		temp = rat;
		
		DB.execute("DELETE FROM ratlist");
		
		for(String elem : temp.keySet())
        {
			DB.execute("INSERT INTO ratlist (name, rating) VALUES ('"+ elem +"', '"+ temp.get(elem) +"')"); 
        }
		
		ratOld = ratNew;
		ratNew = temp.size();
		DB.execute("INSERT INTO updates (name) VALUES ('Write rating-list - "+ratNew+" entries "+(ratNew - ratOld)+" new')");
    }
}